use master
go
create database bank
go

use bank
go
create table UserInfo
(
	CustomerID int primary key identity(1,1),
	CustomerName nvarchar(10) not null,
	PID char(18) check(len(PID)=18 or len(PID)=15) unique not null,
	Telephone char(13) check(len(Telephone)=13 or Telephone like '____-________'),
	Address nvarchar(100) 
)

create table CardInfo
(
	CardID char(18) primary key check(CardId like '1010 3576 ____ ___') default('1010 3576 '+right('0000'+cast(floor(rand()*10000) as varchar),4)+' '+right('000'+cast(floor(rand()*1000) as varchar),3)),
	CurType nchar(10) default('RMB') not null,
	SavingType nchar(4) check(SavingType in('活期','定活两期','定期')),
	OpenDate date default(getdate()) not null,
	Balance money check(Balance>0) not null,
	Pass int check(len(Pass)=6) default(888888) not null,
	IsReportLoss nchar(1) check(IsReportLoss in('是','否')),
	CustomerID int references UserInfo(CustomerID) not null
)

create table TransInfo
(
	TransID int primary key ,
	TransDate date default(getdate()) not null,
	CardID char(18) references CardInfo(CardID) not null,
	TransType nchar(2) check(TransType in('存入','支取')),
	TransMoney money check(TransMoney>0) not null,
	Remark text 
)
go
insert into UserInfo values
('孙悟空',123456789012345,'0716-78989783','北京海淀'),
('沙和尚',421345678912345678,'0478-44223333',''),
('唐僧',321245678912345678,'0478-44443333','')

insert into CardInfo(CardID,SavingType,Balance,CustomerID) values
('1010 3576 1234 567','活期',1000,1),
('1010 3576 1212 117','定期',1,2),
('1010 3576 1212 113','定期',1,3)
select * from UserInfo
select * from CardInfo
select * from TransInfo

--1.将用户“孙悟空”开卡时的初始密码更改为“611234”
update CardInfo set Pass=611234 where CustomerID=1

--2.用两条SQL语句实现孙空悟要取钱(取200)的操作，先向交易信息表插入一条取钱的交易记录，然后在孙悟空账上的余额减200
--注意：先要将用户孙悟空的用户编号找到，再根据用户编号找到卡号，再根据银行卡号来插入交易记录和修改账上余额
insert into TransInfo(TransID,CardID,TransType,TransMoney) values 
(1,'1010 3576 1234 567','支取',200)
update CardInfo set Balance= Balance-200 where CardID=(select CardID from CardInfo where CustomerID=1)

--3.用同上题一样的方法实现沙和尚存钱的操作(存300)
insert into TransInfo(TransID,CardID,TransType,TransMoney) values 
(2,'1010 3576 1212 117','存入',300)
update CardInfo set Balance= Balance+300 where CardID=(select CardID from CardInfo where CustomerID=2)

--4.唐僧的卡丢了，需要挂失，将唐僧的银行卡的是否挂失字段的值改为“是”
update CardInfo set IsReportLoss='是' where CustomerID=(select CustomerID from UserInfo where CustomerID=3)

--5.查询出最近10天开户的银行卡的信息
select * from CardInfo where DateDiff(dd,OpenDate,getdate()) <= 10

--6.查询交易金额最大的银行卡信息，子查询实现
select TransMoney from TransInfo where TransMoney=(select MAX(TransMoney) from TransInfo)

--7.再交易信息表中，将总的交易金额，支取的交易金额，存入的交易金额查询出来并输出显示
-- 显示效果：
--  总交易金额：1400.00
--  支取交易金额：200.00
--  存入交易金额：1200.00
select '总交易金额',SUM(TransMoney)  from TransInfo
union
select '支取的交易金额',SUM(TransMoney)  from TransInfo where TransType='支取'
union
select '存入的交易金额', SUM(TransMoney) from TransInfo where TransType='存入'
--8.催款提醒：假如由于某种业务的需求，每个月末，如果发现卡上账上余额少于200元的，将致电催款，请将卡上余额少于200元的用户的卡号和用户ID查询出来
select CardID 卡号,CustomerID 用户ID , Balance 余额 from CardInfo where Balance<200